package com.example.mapp;

import com.example.mapp.buildSql.ExportMappSql;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.SelectProvider;

import java.util.List;
import java.util.Map;

/**
 * Created by douyx on 2018/03/21.
 */
@Mapper
public interface ExportMapp {

	@Select(" SELECT e.id,et.type_name elevator_type_id,p1.party_name used_party_name,e.build_addr, "
			+ "e.elevator_num,su.user_name safe_user_name,p1.telephone used_party_tel, "
			+ "p2.party_name maintenance_party_name,p2.telephone maintenance_party_tel, "
			+ "e.be_used_date,e.create_company_name,e.build_party_name,e.serial_num, "
			+ "e.elevator_model,CASE WHEN e.insurance_num IS NULL THEN '否' ELSE '是' END insurance_num,e.insurance_date, "
			+ "e.insurance_name,e.angle,e.run_speed,e.floor_station,e.drive_way "
			+ "FROM elevator e "
			+ "LEFT JOIN elevator_type et ON e.elevator_type_id=et.id "
			+ "LEFT JOIN party p1 ON e.used_party_id=p1.id "
			+ "LEFT JOIN party p2 ON e.maintenance_party_id=p2.id "
			+ "LEFT JOIN sys_user su ON e.safe_user_id=su.id "
			+ "WHERE e.elevator_num = #{elevatorNum} ")
	Map<String, Object> findElevatorInfo(String elevatorId) throws Exception;

	@Select("SELECT MAINTENANCE_PARTY_NAME, DRILL_CONTENT,DRILL_PARTY_NAME, "
			+ "CONCAT(SUBSTRING(DRILL_DATE,1,4),'年',SUBSTRING(DRILL_DATE,5,2),'月',SUBSTRING(DRILL_DATE,7,2),'日') DRILL_DATE, "
			+ "CASE WHEN DRILL_TYPE = '1' THEN '√' ELSE '□' END DRILL_TYPE_ONE, "
			+ "CASE WHEN DRILL_TYPE = '2' THEN '√' ELSE '□' END DRILL_TYPE_TWO, "
			+ "CASE WHEN DRILL_TYPE = '3' THEN '√' ELSE '□' END DRILL_TYPE_THREE, "
			+ "CASE WHEN DRILL_TYPE = '4' THEN '√' ELSE '□' END DRILL_TYPE_FOUR, "
			+ "SUBSTRING(DRILL_DATE,1,4) DRILL_DATE_YEAR, "
			+ "SUBSTRING(DRILL_DATE,5,2) DRILL_DATE_MONTH, "
			+ "SUBSTRING(DRILL_DATE,7,2) DRILL_DATE_DAY, "
			+ "SUBSTRING(DRILL_STDT,1,2) DRILL_DATE_HOUR, "
			+ "SUBSTRING(DRILL_EDT,1,2) DRILL_DATE_END_HOUR, "
			+ "COMMUNITY,BUILDING,UNIT,LADDER_NUMBER, "
			+ "H1,M1,H2,M2,H3,M3,H4,M4,H5,M5,H6,M6,H7,M7, "
			+ "IS_PHONE,IS_SURE_PERSON,PERSON_NUM,IS_SURE_STH,IS_EMOTION, "
			+ "IS_TIMELY_REPORT,IS_TIMELY_RESPONSE,IS_TIMELY_REACT,IS_CZ_PERSON,IS_CONNECT, "
			+ "IS_DOSTH,IS_ANFU,IS_DOSTH_AFTER,IS_INTIME,IS_GOUT,IS_QUAN,IS_RIGHT,SLCD,IS_PAICHA,SUMMARY "
			+ "FROM RESCUE_DRILL WHERE ID = #{id} ")
	Map<String, Object> findRescueDrill(String id) throws Exception;

	@Select("SELECT P.PARTY_NAME USED_PARTY_NAME,E.BUILD_ADDR ELEVATOR_ADDRESS, "
			+ "SUBSTRING(ER.RECEIVED_TIME,1,4) RECEIVED_TIME_YEAR, "
			+ "SUBSTRING(ER.RECEIVED_TIME,5,2) RECEIVED_TIME_MONTH, "
			+ "SUBSTRING(ER.RECEIVED_TIME,7,2) RECEIVED_TIME_DAY, "
			+ "SUBSTRING(ER.RECEIVED_TIME,9,2) RECEIVED_TIME_HOUR, "
			+ "SUBSTRING(ER.RECEIVED_TIME,11,2) RECEIVED_TIME_MIN, "
			+ "SUBSTRING(ER.RESCUE_END_TIME,1,4) RECEIVED_TIME_END_YEAR, "
			+ "SUBSTRING(ER.RESCUE_END_TIME,5,2) RECEIVED_TIME_END_MONTH, "
			+ "SUBSTRING(ER.RESCUE_END_TIME,7,2) RECEIVED_TIME_END_DAY, "
			+ "SUBSTRING(ER.RESCUE_END_TIME,9,2) RECEIVED_TIME_END_HOUR, "
			+ "SUBSTRING(ER.RESCUE_END_TIME,11,2) RECEIVED_TIME_END_MIN, "
			+ "ER.ACCIDENT_REASON,CASE WHEN ER.NO_CASUALTIES = '0' THEN '无' ELSE '有' END NO_CASUALTIES, "
			+ "ER.MINOR_WOUND_NUM,ER.INJURED_NUM,ER.DEATH_NUM, "
			+ "CASE WHEN ER.PROTECTIVE LIKE '%1%' THEN '√' ELSE '□' END PROTECTIVE1, "
			+ "CASE WHEN ER.PROTECTIVE LIKE '%2%' THEN '√' ELSE '□' END PROTECTIVE2, "
			+ "CASE WHEN ER.PROTECTIVE LIKE '%3%' THEN '√' ELSE '□' END PROTECTIVE3, "
			+ "CASE WHEN ER.PROTECTIVE LIKE '%4%' THEN '√' ELSE '□' END PROTECTIVE4, "
			+ "ER.PROTECTIVE_OTH,ER.RESCUE_USER_NAME1,ER.RESCUE_USER_NAME2, ER.rescue_party_name, "
			+ "CONCAT(SUBSTRING(SIGNED_TIME,1,4),'年',SUBSTRING(SIGNED_TIME,5,2),'月',SUBSTRING(SIGNED_TIME,7,2),'日') SIGNED_TIME, "
			+ "CONCAT(SUBSTRING(SURED_TIME,1,4),'年',SUBSTRING(SURED_TIME,5,2),'月',SUBSTRING(SURED_TIME,7,2),'日') SURED_TIME "
			+ "FROM EMERGENCY_RESCUE ER "
			+ "INNER JOIN ELEVATOR E ON ER.ELEVATOR_ID = E.ID "
			+ "INNER JOIN PARTY P ON E.USED_PARTY_ID = P.ID "
			+ " WHERE ER.ID = #{id} ")
	Map<String, Object> findEmergencyRescue(String id) throws Exception;

	@Select(" select ITEM,EN_ITEM from maint_fields where ELEVATOR_TYPE = #{elevatorType} AND MAINT_TYPE = #{maintType}")
	List<Map<String, Object>> findFields(Map<String, Object> map) throws Exception;

	@Select(" select ITEM from maint_fields where ELEVATOR_TYPE = #{elevatorType} AND MAINT_TYPE = #{maintType}")
	List<String> findItems(Map<String, Object> map) throws Exception;

	@Select(" select DEMAND from maint_fields where ELEVATOR_TYPE = #{elevatorType} AND MAINT_TYPE = #{maintType}")
	List<String> findDemands(Map<String, Object> map) throws Exception;

	@Select(" SELECT * from traction_half_mon where id = #{id}")
	Map<String, Object> queryTractionHalfMon(String id) throws Exception;

	@Select(" SELECT * from traction_quarter where half_mon_id = #{halfMonId}")
	Map<String, Object> queryTractionQuarter(String halfMonId) throws Exception;

	@Select(" SELECT * from traction_half_year where quarter_id = #{quarterId}")
	Map<String, Object> queryTractionHalfYear(String quarterId) throws Exception;

	@Select(" SELECT * from traction_year where half_year_id = #{halfYearId}")
	Map<String, Object> queryTractionYear(String halfYearId) throws Exception;

	@Select(" SELECT * from hydpress_half_mon where id = #{id}")
	Map<String, Object> queryHydpressHalfMon(String id) throws Exception;

	@Select(" SELECT * from hydpress_quarter where half_mon_id = #{halfMonId}")
	Map<String, Object> queryHydpressQuarter(String halfMonId) throws Exception;

	@Select(" SELECT * from hydpress_half_year where quarter_id = #{quarterId}")
	Map<String, Object> queryHydpressHalfYear(String quarterId) throws Exception;

	@Select(" SELECT * from hydpress_year where half_year_id = #{halfYearId}")
	Map<String, Object> queryHydpressYear(String halfYearId) throws Exception;

	@Select(" SELECT * from escalator_half_mon where id = #{id}")
	Map<String, Object> queryEscalatorHalfMon(String id) throws Exception;

	@Select(" SELECT * from escalator_quarter where half_mon_id = #{halfMonId}")
	Map<String, Object> queryEscalatorQuarter(String halfMonId) throws Exception;

	@Select(" SELECT * from escalator_half_year where quarter_id = #{quarterId}")
	Map<String, Object> queryEscalatorHalfYear(String quarterId) throws Exception;

	@Select(" SELECT * from escalator_year where half_year_id = #{halfYearId}")
	Map<String, Object> queryEscalatorYear(String halfYearId) throws Exception;

	@Select(" SELECT * from debris_half_mon where id = #{id}")
	Map<String, Object> queryDebrisHalfMon(String id) throws Exception;

	@Select(" SELECT * from debris_quarter where half_mon_id = #{halfMonId}")
	Map<String, Object> queryDebrisQuarter(String halfMonId) throws Exception;

	@Select(" SELECT * from debris_half_year where quarter_id = #{quarterId}")
	Map<String, Object> queryDebrisHalfYear(String quarterId) throws Exception;

	@Select(" SELECT * from debris_year where half_year_id = #{halfYearId}")
	Map<String, Object> queryDebrisYear(String halfYearId) throws Exception;
	
	@Select(" select a.*, b.user_name as check_user_name " +
            " from daily_inspection a " +
            " left join sys_user b on a.check_user_id = b.id " +
            " where a.elevator_id=#{elevatorId} and a.check_start_dt like CONCAT(#{month},'%') order by a.check_start_dt desc")
	List<Map<String, Object>> queryById(Map<String, Object> map) throws Exception;
	
	@Select(" select CASE WHEN check_start_dt IS NULL THEN '' ELSE LEFT(check_start_dt,8) END check_start_dt " +
            " from daily_inspection a " +
            " where a.elevator_id=#{elevatorId} and a.check_start_dt like CONCAT(#{month},'%') order by a.check_start_dt desc")
	List<String> queryDate(Map<String, Object> map) throws Exception;

	@SelectProvider(type = ExportMappSql.class,method = "findTractionHalfMon")
	List<Map<String, Object>> queryTractionHalfMonsByElevatorNum(Map<String, Object> map) throws Exception;

	@SelectProvider(type = ExportMappSql.class,method = "findEmergencyRescue")
	List<Map<String,Object>> queryEmergencyRescueByElevatorNum(Map<String,Object> map) throws  Exception;

	@SelectProvider(type = ExportMappSql.class,method = "findSelfCheck")
	List<Map<String,Object>> querySelfCheckByElevatorNum(Map<String,Object> map) throws Exception;

	/**
	 * 批量导出维修作业，查询某台电梯的维修记录
	 * @param map
	 * @return
	 * @throws Exception
	 */
	@SelectProvider(type = ExportMappSql.class,method = "findRepairInfo")
	List<Map<String,Object>> queryRepairInfoByElevatorNum(Map<String,Object> map) throws Exception;

	@Select(" SELECT su.user_name as 'name1',suo.user_name as 'name2' from traction_half_mon thm " +
			"LEFT JOIN sys_user su ON thm.maintenance_user_id1  = su.id " +
			"LEFT JOIN sys_user suo ON thm.maintenance_user_id2  = suo.id " +
			"where thm.id = #{halfMonId} ")
	Map<String, String> queryMaintUsers(String halfMonId);

	@Select(" SELECT su.user_name as 'name1',suo.user_name as 'name2' from ${table} thm " +
			"LEFT JOIN sys_user su ON thm.maintenance_user_id1  = su.id " +
			"LEFT JOIN sys_user suo ON thm.maintenance_user_id2  = suo.id " +
			"where thm.id = #{halfMonId} ")
	Map<String,String> queryNameByMonId(@Param("table") String table,@Param("halfMonId") String halfMonId);

	@Select(" SELECT p.party_name as 'partyName' FROM ${table} a LEFT JOIN party p ON a.maintenance_party_id = p.id WHERE a.id = #{id}  ")
	Map<String,String> queryMaintUsedPartyById(Map<String, Object> param);
}
